package findgood.tools;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import findgood.entity.Guaci;
import findgood.entity.Percent;
import findgood.entity.Yaoci;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.TimeUnit;

/**
 * Excel工具类
 */
@Slf4j
public class ExcelUtil {
	/**
	 * Excel表格导出
	 * @param response    HttpServletResponse对象
	 * @param excelData   Excel表格的数据
	 * @param sheetName   sheet的名字
	 * @param fileName    导出Excel的文件名
	 * @param columnWidth Excel表格的宽度，建议为15
	 * @throws IOException 抛IO异常
	 */
	public static void exportExcel(HttpServletResponse response, List<List<String>> excelData, String sheetName,
			String fileName, int columnWidth) throws IOException {
		// 声明一个工作簿
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格，设置表格名称
		HSSFSheet sheet = workbook.createSheet(sheetName);
		// 设置表格列宽度
		sheet.setDefaultColumnWidth(columnWidth);
		// 写入List<List<String>>中的数据
		int rowIndex = 0;
		for (List<String> data : excelData) {
			// 创建一个row行，然后自增1
			HSSFRow row = sheet.createRow(rowIndex++);
			// 遍历添加本行数据
			for (int i = 0; i < data.size(); i++) {
				// 创建一个单元格
				HSSFCell cell = row.createCell(i);
				// 创建一个内容对象
				HSSFRichTextString text = new HSSFRichTextString(data.get(i));
				// 将内容对象的文字内容写入到单元格中
				cell.setCellValue(text);
			}
		}
		// 准备将Excel的输出流通过response输出到页面下载
		// 八进制输出流
		response.setContentType("application/octet-stream");
		// 设置导出Excel的名称
		response.setHeader("Content-disposition", "attachment;filename=" + fileName);
		// 刷新缓冲
		response.flushBuffer();
		// workbook将Excel写入到response的输出流中，供页面下载该Excel文件
		workbook.write(response.getOutputStream());
		workbook.close();
	}
	public static void setTitle(Sheet sheet) {
		List<String> a=new ArrayList<String>();
		a.add("查询牌子");a.add("查询名称");a.add("申报品名");a.add("价格");a.add("税号");a.add("单位");
		a.add("单位代码");a.add("完税价格");a.add("税率");a.add("参考完税价格");a.add("翻译");a.add("重量");
			Row row = sheet.createRow((short)0);
			for (int j = 0; j < 12; j++) {
				Cell cell = row.createCell((short)j);
				cell.setCellValue(a.get(j));
			}
	}
	public static List<Percent> Fnengliang(BufferedInputStream bufferedInputStream) {
		try {
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(bufferedInputStream);
			XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
			List<Percent> aList = new ArrayList<Percent>();
			int id = 1;
			for (Row row : sheet){
				log.info("保存数据行数:" +id);
				Percent single = new Percent();
				TimeUnit.MILLISECONDS.sleep(20);// 毫秒
				single.setId(id);
				id++;
				if (row.getCell(0) != null) {
						int a1=(int) row.getCell(1).getNumericCellValue();
						single.setJia(a1);
						int a2=(int) row.getCell(2).getNumericCellValue();
						single.setYi(a2);
						int a3=(int) row.getCell(3).getNumericCellValue();
						single.setBing(a3);
						int a4=(int) row.getCell(4).getNumericCellValue();
						single.setDing(a4);
						int a5=(int) row.getCell(5).getNumericCellValue();
						single.setWu(a5);
						
						int a6=(int) row.getCell(6).getNumericCellValue();
						single.setJi(a6);
						int a7=(int) row.getCell(7).getNumericCellValue();
						single.setGeng(a7);
						int a8=(int) row.getCell(8).getNumericCellValue();
						single.setXin(a8);
						int a9=(int) row.getCell(9).getNumericCellValue();
						single.setRen(a9);
						int a10=(int) row.getCell(10).getNumericCellValue();
						single.setGui(a10);
						
						int a11=(int) row.getCell(11).getNumericCellValue();
						single.setYin(a11);
						int a12=(int) row.getCell(12).getNumericCellValue();
						single.setMao(a12);
						
						int a13=(int) row.getCell(13).getNumericCellValue();
						single.setChen(a13);
						int a14=(int) row.getCell(14).getNumericCellValue();
						single.setSi(a14);
						int a15=(int) row.getCell(15).getNumericCellValue();
						single.setWuu(a15);
						int a16=(int) row.getCell(16).getNumericCellValue();
						single.setWei(a16);
						int a17=(int) row.getCell(17).getNumericCellValue();
						single.setShen(a17);
						
						int a18=(int) row.getCell(18).getNumericCellValue();
						single.setYou(a18);
						int a19=(int) row.getCell(19).getNumericCellValue();
						single.setXu(a19);
						int a20=(int) row.getCell(20).getNumericCellValue();
						single.setHai(a20);
						int a21=(int) row.getCell(21).getNumericCellValue();
						single.setZi(a21);
						int a22=(int) row.getCell(22).getNumericCellValue();
						single.setChou(a22);
						aList.add(single);
				}
			}
			xssfWorkbook.close();
			return aList;
		} catch (Exception e) {
			log.error("保存数据解析数据异常:" + e.getMessage(), e);
		}
		return null;
	}
	public static List<Guaci> Fgua(BufferedInputStream bufferedInputStream) {
		try {
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(bufferedInputStream);
			XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
			List<Guaci> aList = new ArrayList<Guaci>();
			int id = 1;
			for (Row row : sheet){
				log.info("保存数据行数:" +id);
				Guaci single = new Guaci();
				TimeUnit.MILLISECONDS.sleep(20);// 毫秒
				single.setId(id);
				id++;
				if (row.getCell(0) != null) {
					if (row.getCell(2) != null) {
						String all=row.getCell(2).getStringCellValue();
						single.setGname(all);
					} else {
						single.setGname("空");
					}
					if (row.getCell(3) != null) {
						String b=row.getCell(3).getStringCellValue();
							single.setGci(b);
					}  else {
						single.setGci("空");
					}
					if (row.getCell(4) != null) {
						single.setGfanyi(row.getCell(4).getStringCellValue());
					}else {
						single.setGfanyi("空");
					}
					aList.add(single);
				}
			}
			xssfWorkbook.close();
			return aList;
		} catch (Exception e) {
			log.error("保存数据解析数据异常:" + e.getMessage(), e);
		}
		return null;
	}
	public static List<Yaoci> Fyao(BufferedInputStream bufferedInputStream) {
		try {
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(bufferedInputStream);
			XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
			List<Yaoci> aList = new ArrayList<Yaoci>();
			int id = 1;
			for (Row row : sheet) {
				log.info("保存数据行数:" +id);
				Yaoci single = new Yaoci();
				TimeUnit.MILLISECONDS.sleep(2);// 毫秒
				single.setId(id);
				id++;
				if (row.getCell(1) != null) {
						int ww=(int) row.getCell(1).getNumericCellValue();
						single.setGid(ww);
					if (row.getCell(2) != null) {
						String all=row.getCell(2).getStringCellValue();
						single.setYname(all);
					}else {
						single.setYname("空");
					} 
					if (row.getCell(3) != null) {
						String all=row.getCell(3).getStringCellValue();
						single.setYci(all);
					} else {
						single.setYci("空");
					} 
					if (row.getCell(4) != null) {
						String b=row.getCell(4).getStringCellValue();
							single.setYfanyi(b);
					} else {
						single.setYfanyi("空");
					} 
					if (row.getCell(5) != null) {
						single.setWuyifan(row.getCell(5).getStringCellValue());
					} else {
						single.setWuyifan("空");
					} 
					aList.add(single);
				}
			}
			xssfWorkbook.close();
			return aList;
		} catch (Exception e) {
			log.error("保存数据解析数据异常:" + e.getMessage(), e);
		}
		return null;
	}
}
